﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data ;

namespace Mmht.DB
{
    public class StudentDB
    {
        public DataTable FindByAccount(string account) 
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from student where account = '"+account .ToString ()+"'";
            operate op = new operate();
            dt = op.Query(sql);
            return dt;
        }
        public DataRow FindById(int id)
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from student where id = " + id.ToString() + "";
            operate op = new operate();
            dt = op.Query(sql);
            if (dt == null)
            {
                return null;
            }
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }
        public bool InsertStudent(string account,string password,string created_by) 
        {
            DateTime now = DateTime.Now;
            string sql = string.Empty;
            string remark = "student";
            sql = "insert into student(account,password,created_by,created_at,remark)values('"+account .ToString ()+"','"+password .ToString ()+"','"+created_by .ToString ()+"','"+now+"','"+remark +"')";
            operate op = new operate();
            bool bl = op.Execute(sql);
            return bl;
        }
        public bool UpdateStudent(int id, string name, string password, string email, string tel)
        {
            string sql = string.Empty;
            if (password.Trim().Length == 0)
            {
                sql = "update student set name = '" + name.ToString() + "',e_mail = '" + email.ToString() + "',tel = '" + tel.ToString() + "' where id = " + id.ToString() + "";
            }
            else
            {
                sql = "update student set name = '" + name.ToString() + "',password = '" + password.ToString() + "',e_mail = '" + email.ToString() + "',tel = '" + tel.ToString() + "' where id = " + id.ToString() + "";
            }
            operate op = new operate();
            bool bl = op.Execute(sql);
            return bl;
        }
        public DataTable FindStudent() 
        {
            string sql = string.Empty;
            sql = "select * from student";
            operate op = new operate();
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            return dt;
        }
        public bool DelStudent(int id) 
        {
            string sql = string.Empty;
            sql = "delete from student where id="+id.ToString ()+"";
            operate op = new operate();
            bool bl = op.Execute(sql);
            return bl;
        }

        public bool DelAllStudent()
        {
            string sql = string.Empty;
            sql = "delete from student";
            operate op = new operate();
            bool bl = op.Execute(sql);
            return bl;
        }
    }
}
